﻿using NHibernateVertica;
using System;
using System.Collections.Generic;
using System.Linq;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using System.ComponentModel;
using System.Data;
using com.nm.Attributes;
using com.nm.library.Helper;
using george.Data;
using VerticaAPI.model;
using george.Web;
using com.nm.Cache;
using com.nm.Helper;
using Vertica.Data.VerticaClient;
using VerticaAPI.comm;
using com.nm.library;
using BaseAPI;

namespace VerticaAPI
{
    [Description("店铺信息")]
    public class DwShopHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        [MustLogin]
        [Description("店铺信息列表")]
        [ReturnType(typeof(PaperData<DwShopModel.ListModel>))]
        public ApiResult GetDwShopList([ParamDescription("查询条件")] string Keys = "",
            [ParamDescription("商品区域")] string AreaID = "",
            [ParamDescription("省份")] string ProID = "",
            [ParamDescription("城市")] string CityID = "",
            [ParamDescription("店铺等级")] string ShopLevelCode = "",
            [ParamDescription("店铺状态")] string ShopState = "",
            [ParamDescription("零售区域")] string OperationAreaID = "",
            [ParamDescription("符号")] string Symbol = "",
            [ParamDescription("物流天数")] string TransportDay = "",
            [ParamDescription("页码")] int Page = 1,
            [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var count = ReturnTotalDwShop(Keys, AreaID, ProID, CityID, ShopLevelCode, ShopState, OperationAreaID, Symbol, TransportDay);
                var result = PaperData<DwShopModel.ListModel>.Create(count, Size, Page);
                var dt = ReturnDwShopList(Page, Size, Keys, AreaID, ProID, CityID, ShopLevelCode, ShopState,
                    OperationAreaID, Symbol, TransportDay);
                var list = ModelConvertHelper<DwShopModel.ListModel>.ConvertToModel(dt);
                result.Items = list;
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="Keys"></param>
        /// <param name="Leavel"></param>
        /// <param name="ProID"></param>
        /// <param name="CityID"></param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        public DataTable ReturnDwShopList(int Page, int Size, string Keys, string AreaID, string ProID,
            string CityID, string ShopLevelCode, string ShopState,
            string OperationAreaID, string Symbol, string TransportDay)
        {
            try
            {
                //var shopList = Common.GetShopList(this.UserId);{(!string.IsNullOrWhiteSpace(shopList)? "and dw.ShopID in("+shopList+")":"")}
                var sql =
                    $@"SELECT table1.*
FROM (SELECT dw.*,u.UserName,eba.AreaName,ebap.AreaID as ParentAreaID,ebap.AreaName as ParentAreaName,eboa.OperationAreaName,
gv.dictValue as DistrictID,gv.dictName as DistrictName,ebs.StockName,ebs.StockCode
,ROW_NUMBER()OVER(ORDER BY dw.KdDate desc) AS Row 
FROM ndw.ndw_Shop dw
left join nods.nods_erp_Bas_Area eba on eba.AreaID = dw.AreaID and dw.ExtrSource =eba.ExtrSource
left join nods.nods_erp_Bas_Area ebap on ebap.AreaID=eba.ParentID and dw.ExtrSource =ebap.ExtrSource
left join nods.nods_erp_Bas_OperationArea eboa on eboa.OperationAreaID=dw.OperationAreaID and eboa.ExtrSource = dw.ExtrSource and eboa.CompanyID = dw.CompanyID
left join ndw.ndw_AreaProCity apc on dw.AreaID = apc.AreaID and apc.ExtrSource = dw.ExtrSource and apc.CompanyID = dw.CompanyID
left join   nods.nods_erp_Bas_Stock  ebs on apc.StockID=ebs.StockID  and apc.ExtrSource = ebs.ExtrSource and apc.CompanyID = ebs.CompanyID
left join  ndw.ndw_global_dictionary gv on apc.ParentAreaID = gv.dictValue    and apc.ExtrSource = gv.ExtrSource and apc.CompanyID = gv.CompanyID
left join nods.nods_erp_Sys_User u on u.UserCode = dw.UserCode  and dw.ExtrSource =u.ExtrSource
where 1=1 and  dw.ExtrSource='{UserInfo.UserExtrSource}' and dw.CompanyID='{UserInfo.UserCompanyId}' 
{(string.IsNullOrEmpty(Keys)
                        ? ""
                        : string.Format(" and (dw.ShopCode like '%{0}%' or dw.ShopName like '%{0}%') ", Keys))} 
{(string.IsNullOrEmpty(ProID) ? "" : $" and ebap.AreaID = '{ProID}' ")} {(string.IsNullOrEmpty(CityID)
                        ? ""
                        : $" and dw.AreaID = '{CityID}' ")} 
{(string.IsNullOrEmpty(ShopLevelCode) ? "" : $" and dw.ShopLevelCode = '{ShopLevelCode}' ")} 
{(string.IsNullOrEmpty(ShopState) ? "" : $" and dw.ShopState = '{ShopState}' ")} 
{(string.IsNullOrEmpty(OperationAreaID) ? "" : $" and dw.OperationAreaID = '{OperationAreaID}' ")} 
{(string.IsNullOrEmpty(AreaID) ? "" : $" and gv.dictValue = '{AreaID}' ")}
{(string.IsNullOrEmpty(TransportDay) ? "" : $@" and dw.TransportDay {Symbol} {TransportDay} ")}
) AS table1
WHERE Row BETWEEN ({Page}-1)*{Size}+1 AND {Page}*{Size}   order by table1.KdDate desc, table1.shopcode ";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 返回总条数
        /// </summary>
        /// <param name="Keys"></param>
        /// <param name="AreaCode"></param>
        /// <param name="ProCode"></param>
        /// <param name="CityCode"></param>
        /// <returns></returns>
        public int ReturnTotalDwShop(string Keys, string AreaID, string ProID,
            string CityID, string ShopLevelCode, string ShopState,
            string OperationAreaID, string Symbol, string TransportDay)
        {
            try
            {
                //var shopList = Common.GetShopList(this.UserId);{(!string.IsNullOrWhiteSpace(shopList)? "and dw.ShopID in("+shopList+")":"")}
                var sql = $@"select count(1) FROM ndw.ndw_Shop dw
left join nods.nods_erp_Bas_Area eba on eba.AreaID = dw.AreaID and dw.ExtrSource =eba.ExtrSource
left join nods.nods_erp_Bas_Area ebap on ebap.AreaID=eba.ParentID and dw.ExtrSource =ebap.ExtrSource
left join nods.nods_erp_Bas_OperationArea eboa on eboa.OperationAreaID=dw.OperationAreaID and eboa.ExtrSource = dw.ExtrSource and eboa.CompanyID = dw.CompanyID
left join ndw.ndw_AreaProCity apc on dw.AreaID = apc.AreaID and apc.ExtrSource = dw.ExtrSource and apc.CompanyID = dw.CompanyID
left join   nods.nods_erp_Bas_Stock  ebs on apc.StockID=ebs.StockID  and apc.ExtrSource = ebs.ExtrSource and apc.CompanyID = ebs.CompanyID
left join  ndw.ndw_global_dictionary gv on apc.ParentAreaID = gv.dictValue    and apc.ExtrSource = gv.ExtrSource and apc.CompanyID = gv.CompanyID
where 1=1  and  dw.ExtrSource='{UserInfo.UserExtrSource}' and dw.CompanyID='{UserInfo.UserCompanyId}' 
{(string.IsNullOrEmpty(Keys)
                        ? ""
                        : string.Format(" and (dw.ShopCode like '%{0}%' or dw.ShopName like '%{0}%') ", Keys))} 
{(string.IsNullOrEmpty(ProID) ? "" : $" and ebap.AreaID = '{ProID}' ")} {(string.IsNullOrEmpty(CityID)
                        ? ""
                        : $" and dw.AreaID = '{CityID}' ")} 
{(string.IsNullOrEmpty(ShopLevelCode) ? "" : $" and dw.ShopLevelCode = '{ShopLevelCode}' ")} 
{(string.IsNullOrEmpty(ShopState) ? "" : $" and dw.ShopState = '{ShopState}' ")} 
{(string.IsNullOrEmpty(OperationAreaID) ? "" : $" and dw.OperationAreaID = '{OperationAreaID}' ")} 
{(string.IsNullOrEmpty(AreaID) ? "" : $" and gv.dictValue = '{AreaID}' ")}
{(string.IsNullOrEmpty(TransportDay) ? "" : $@" and dw.TransportDay {Symbol} {TransportDay} ")}";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        [JsonType]
        [MustLogin]
        [Description("批量修改店铺信息")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult ModifyDwShopList([ParamDescription("关键字")] DwShopModel.ModifyModel param)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sqlUpdate = @"UPDATE ndw.ndw_Shop SET {0} where  ShopCode in ({1}) and {2}";
                var con = @"";
                if (param == null) return Error("参数对象为空!");
                if (param.list == null) return Error("参数对象为空!");
                if (param.newReplenishDay != null)
                {
                    con += $@" ReplenishDay={param.newReplenishDay} ";
                    if (param.newBillandPickDay != null)
                    {
                        con += $@" , BillandPickDay={param.newBillandPickDay} ";
                    }
                    if (param.newTransportDay != null)
                    {
                        con += $@" , TransportDay={param.newTransportDay} ";
                    }
                    if (param.newShopRate != null)
                    {
                        con += $@" , ShopRate={param.newShopRate}";
                    }
                }
                else if (param.newBillandPickDay != null)
                {
                    con += $@" BillandPickDay={param.newBillandPickDay} ";
                    if (param.newTransportDay != null)
                    {
                        con += $@" , TransportDay={param.newTransportDay} ";
                    }
                    if (param.newShopRate != null)
                    {
                        con += $@" , ShopRate={param.newShopRate}";
                    }
                }
                else if (param.newTransportDay != null)
                {
                    con += $@" TransportDay={param.newTransportDay} ";
                    if (param.newShopRate != null)
                    {
                        con += $@" , ShopRate={param.newShopRate}";
                    }
                }
                else if (param.newShopRate != null)
                {
                    con += $@" ShopRate={param.newShopRate}";
                }
                else
                {
                    if (!string.IsNullOrEmpty(param.newSuppleWeekText))
                    {
                        con += $@" SuppleWeekText='{param.newSuppleWeekText}' ";
                    }
                    else
                    {
                        con += $@" SuppleWeekText='' ";
                    }
                }
                var list = new List<string>();
                foreach (var item in param.list.Select(d => d.ShopCode).ToList())
                {
                    var i = "'" + item + "'";
                    list.Add(i);
                }

                var userCodeSql = string.IsNullOrWhiteSpace(param.UserCode) ? "" : $",UserCode='{param.UserCode}'";

                var cmdList = new List<CommandInfo>()
                {
                    new CommandInfo()
                    {
                        CommandText = string.Format(sqlUpdate, con + userCodeSql, string.Join(",", list),$" ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}' "),
                    }
                };
                Helper.ExecuteSqlTran(cmdList);
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        [JsonType]
        [MustLogin]
        [Description("修改店铺信息")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult ModifyDwShop([ParamDescription("关键字")] DwShopModel.ModifyModel param)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sqlUpdate = @"UPDATE ndw.ndw_Shop
SET {0}
where ShopCode ='{1}' and {2}";
                if (param == null) return Error("参数对象为空!");
                if (param.list == null) return Error("参数对象为空!");
                if (param.list.Count <= 0) return Error("参数对象为空!");
                var cmdList = new List<CommandInfo>();
                foreach (var item in param.list)
                {
                    var con = @"";
                    if (item.ReplenishDay != null)
                    {
                        con += string.Format(@" ReplenishDay={0} ", item.ReplenishDay);
                        if (item.BillandPickDay != null)
                        {
                            con += string.Format(@" , BillandPickDay={0} ", item.BillandPickDay);
                        }
                        if (item.TransportDay != null)
                        {
                            con += string.Format(@" , TransportDay={0} ", item.TransportDay);
                        }
                        if (item.ShopRate != null)
                        {
                            con += $@" , ShopRate={item.ShopRate}";
                        }
                    }
                    else if (item.BillandPickDay != null)
                    {
                        con += string.Format(@" BillandPickDay={0} ", item.BillandPickDay);
                        if (item.TransportDay != null)
                        {
                            con += string.Format(@" , TransportDay={0} ", item.TransportDay);
                        }
                        if (item.ShopRate != null)
                        {
                            con += $@" , ShopRate={item.ShopRate}";
                        }
                    }
                    else if (item.TransportDay != null)
                    {
                        con += string.Format(@" TransportDay={0} ", item.TransportDay);
                        if (item.ShopRate != null)
                        {
                            con += $@" , ShopRate={item.ShopRate}";
                        }
                    }
                    else if (item.ShopRate != null)
                    {
                        con += $@" ShopRate={item.ShopRate}";
                    }

                    var userCodeSql = string.IsNullOrWhiteSpace(param.UserCode) ? "" : $",UserCode='{param.UserCode}'";
                    var cmdi = new CommandInfo()
                    {
                        CommandText = string.Format(sqlUpdate, con + userCodeSql, item.ShopCode, $" ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}' "),
                    };
                    cmdList.Add(cmdi);
                }
                Helper.ExecuteSqlTran(cmdList);
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }


        [JsonType]
        [MustLogin]
        [Description("批量修改店铺设置配补调")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult ModifyDwShopPDB([ParamDescription("关键字")] DwShopModel.ModifyModel param)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sqlUpdate = @"UPDATE ndw.ndw_Shop SET {0} where  ShopCode in ({1}) and {2}";
                var con = @"CompanyID = CompanyID";
                if (param == null) return Error("参数对象为空!");
                if (param.list == null) return Error("参数对象为空!");
                if (param.IsMoveShop != -1)
                {
                    con += $@",IsMoveShop={param.IsMoveShop}";
                }
                if (param.IsInShop != -1)
                {
                    con += $@",IsInShop={param.IsInShop} ";
                }
                if (param.ISDwMvShop != -1)
                {
                    con += $@",ISDwMvShop={param.ISDwMvShop} ";
                }
                var list = new List<string>();
                foreach (var item in param.list.Select(d => d.ShopCode).ToList())
                {
                    var i = "'" + item + "'";
                    list.Add(i);
                }

                //var userCodeSql = string.IsNullOrWhiteSpace(param.UserCode) ? "" : $",UserCode='{param.UserCode}'";

                var cmdList = new List<CommandInfo>()
                {
                    new CommandInfo()
                    {
                        CommandText = string.Format(sqlUpdate, con, string.Join(",", list),$" ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}' "),
                    }
                };
                Helper.ExecuteSqlTran(cmdList);
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        [MustLogin]
        [Description("店铺等级或店铺状态列表")]
        [ReturnType(typeof(IList<ShopAndCustBaseInfo.NeceModel>))]
        public ApiResult GetShopAndCustBaseInfo([ParamDescription("ShopState/ShopLevel")] string InfoType = "ShopLevel")
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var cacheKey = $"all-ShopAndCustBaseInfo-{InfoType}-{UserInfo.UserExtrSource}-list";
                var result = RedisCache.GetAndDecodeJson<IList<ShopAndCustBaseInfo.NeceModel>>(cacheKey);
                if (result == null)
                {
                    var dt = ReturnShopAndCustBaseInfo(InfoType);
                    result = ModelConvertHelper<ShopAndCustBaseInfo.NeceModel>.ConvertToModel(dt);
                    RedisCache.MinString(cacheKey, result.ToJson(), 30);
                }
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 返回店铺等级或店铺状态
        /// </summary>
        /// <remarks>
        /// 修改时间:2017-07-28
        /// 修改内容:nods.nods_erp_SD_Bas_ShopAndCustBaseInfo表中的CompanyID不再做为查询条件,移除
        /// </remarks>
        /// <returns></returns>
        public DataTable ReturnShopAndCustBaseInfo(string InfoType)
        {
            try
            {
                var sql = string.Format(@" SELECT InfoType, InfoID, InfoName
FROM nods.nods_erp_SD_Bas_ShopAndCustBaseInfo 
where InfoType='{0}'  and {1}", InfoType, $" ExtrSource='{UserInfo.UserExtrSource}' ");
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return dt;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        [MustLogin]
        [Description("零售分部列表")]
        [ReturnType(typeof(IList<OdsErpBasOperationArea.NeceModel>))]
        public ApiResult GetBasOperationArea()
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var cacheKey = $"all-BasOperationArea-{UserInfo.UserExtrSource}-list";
                var result = RedisCache.GetAndDecodeJson<IList<OdsErpBasOperationArea.NeceModel>>(cacheKey);
                if (result == null)
                {
                    var dt = ReturnBasOperationArea();
                    result = ModelConvertHelper<OdsErpBasOperationArea.NeceModel>.ConvertToModel(dt);
                    RedisCache.MinString(cacheKey, result.ToJson(), 30);
                }
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #region GetBasOperationArea2 根据用户店铺权限获取零售分部列表

        [MustLogin]
        [Description("根据用户店铺权限获取零售分部列表")]
        [ReturnType(typeof(IList<OdsErpBasOperationArea.NeceModel>))]
        public ApiResult GetBasOperationArea2()
        {
            try
            {
                var uid = Convert.ToInt32(UserInfo.UserID);
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var list = new OperationAreaUserHandler().GetOperationList(uid);
                var items = string.Join("','", list);

                string sql = $@"SELECT * FROM nods.nods_erp_Bas_OperationArea
where  
ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}' and level=1 and IsDetail=0 and OperationAreaID in('{items}') 
ORDER by OperationAreaID";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var result = ModelConvertHelper<OdsErpBasOperationArea.NeceModel>.ConvertToModel(dt);
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion



        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <returns></returns>
        public DataTable ReturnBasOperationArea()
        {
            try
            {
                string sql =
                    $@"SELECT *
FROM nods.nods_erp_Bas_OperationArea
where  ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}' and level=1 and IsDetail=0 ORDER by OperationAreaID";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return dt;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        [MustLogin]
        [Description("根据城市修改店铺物流天数")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult ModTransportDayByCity([ParamDescription("城市ID")]string CityID, [ParamDescription("物流天数")] long TransportDay)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                if (string.IsNullOrEmpty(CityID)) return Error("请选择城市");
                var sql = $@"update ndw.ndw_Shop set TransportDay={TransportDay} where  ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}'  and AreaID={CityID}";
                var cmdList = new List<CommandInfo>()
                {
                    new CommandInfo()
                    {
                        CommandText = sql,
                    }
                };
                Helper.ExecuteSqlTran(cmdList);
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        [MustLogin]
        [Description("根据城市获取店铺物流天数")]
        [ReturnType(typeof(IList<TransportDayModel.NeceModel>))]
        public ApiResult GetTransportDayByCity(string CityName)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var dt = ReturnTransportDayByCity(CityName);
                var result = ModelConvertHelper<TransportDayModel.NeceModel>.ConvertToModel(dt);
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="CityID"></param>
        /// <returns></returns>
        public DataTable ReturnTransportDayByCity(string CityName)
        {
            try
            {
                var sql = $@"select distinct eba.AreaID,AreaName,dw.TransportDay from nods.nods_erp_Bas_Area eba
inner join dw.dw_Shop dw on eba.AreaID=dw.AreaID  and dw.ExtrSource =eba.ExtrSource
where eba.Level = 2 and  dw.ExtrSource='{UserInfo.UserExtrSource}' and dw.CompanyID='{UserInfo.UserCompanyId}' ";
                if (!string.IsNullOrEmpty(CityName))
                {
                    sql += $@" and eba.AreaName like '%{CityName}%' ";
                }
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        [MustLogin]
        [Description("店铺信息列表")]
        [ReturnType(typeof(IList<DwShopModel.NeceModel>))]
        public ApiResult GetAllDwShop([ParamDescription("发货仓库")] string FHStockID, [ParamDescription("查询条件")] string Keys = @"", [ParamDescription("省份")] string ProID = "",
            [ParamDescription("城市")] string CityID = "", [ParamDescription("店铺级别")] string ShopLevelCode = "")
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var dt = ReturnAllDwShop(FHStockID, Keys, ProID, CityID, ShopLevelCode);
                var result = ModelConvertHelper<DwShopModel.NeceModel>.ConvertToModel(dt);
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="Keys"></param>
        /// <returns></returns>
        public DataTable ReturnAllDwShop(string FHStockID, string Keys, string ProID, string CityID, string ShopLevelCode)
        {
            try
            {
                var sql = $@"select ds.* from dw.dw_Shop ds
inner join ods.ods_erp_Bas_Area eba on ds.AreaID=eba.AreaID   and ds.ExtrSource =eba.ExtrSource
inner join dw.dw_AreaProCity apc on eba.AreaID=apc.AreaID  and apc.ExtrSource = eba.ExtrSource and apc.CompanyID = '{UserInfo.UserCompanyId}' 
where  ds.ExtrSource='{UserInfo.UserExtrSource}' and ds.CompanyID='{UserInfo.UserCompanyId}' and (ds.ShopState='正常营业' or ds.ShopState='待开业') and apc.StockID ='{FHStockID}' and ShopName like '%{Keys}%' or ShopCode like '%{Keys}%')
{(string.IsNullOrEmpty(ProID) ? "" : $@" and eba.ParentID ={ProID} ")}
{(string.IsNullOrEmpty(CityID) ? "" : $@" and ds.AreaID ={CityID} ")}
{(string.IsNullOrEmpty(ShopLevelCode) ? "" : $@" and ds.ShopLevelCode ={ShopLevelCode} ")}";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 返回所有店铺信息
        /// </summary>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-03-15
        public IList<DwShopModel.NeceModel> ReturnDwShopList()
        {
            try
            {
                var sql = @"select * from dw.dw_shop";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return ModelConvertHelper<DwShopModel.NeceModel>.ConvertToModel(dt);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        [MustLogin]
        [Description("自动填充(商店)")]
        [ReturnType(typeof(IList<DwShopModel.ShopDownListModel>))]
        public ApiResult GetShopDownList(
            [ParamDescription("商品区域")] string ParentAreaID = "",
            [ParamDescription("店铺编码或名称")] string Shop = "")
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            string sql = $@"select 
ShopCode,ShopName
--gv.dictValue as DistrictValue,
--gv.dictName   as DistrictName
from ndw.ndw_Shop dw 
	left join ndw.ndw_AreaProCity apc on dw.AreaID = apc.AreaID and apc.ExtrSource = dw.ExtrSource and apc.CompanyID = dw.CompanyID
	left join ndw.ndw_global_dictionary gv on apc.ParentAreaID = gv.dictValue    and apc.ExtrSource = gv.ExtrSource and apc.CompanyID = gv.CompanyID
where dw.ExtrSource='{UserInfo.UserExtrSource}' and dw.CompanyID='{UserInfo.UserCompanyId}'  and
	gv.dictValue = '{ParentAreaID}' and (ShopCode ilike '%{Shop}%' or ShopName ilike '%{Shop}%')";

            var dt = Helper.ExecuteDataTable(new VerticaCommand(sql));
            var list = ModelConvertHelper<DwShopModel.ShopDownListModel>.ConvertToModel(dt);
            return Success(list);
        }

        [MustLogin]
        [Description("自动填充(商店)")]
        [ReturnType(typeof(IList<DwShopModel.ShopDownListModel>))]
        public ApiResult GetShopDownList2(
            [ParamDescription("店铺编码或名称")] string Shop = "")
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            string sql = $@"select ShopID,ShopCode,ShopName
from ndw.ndw_Shop dw 
where dw.ExtrSource='{UserInfo.UserExtrSource}' and dw.CompanyID='{UserInfo.UserCompanyId}'  and 
ShopCode ilike '%{Shop}%' or ShopName ilike '%{Shop}%'";

            var dt = Helper.ExecuteDataTable(new VerticaCommand(sql));
            var list = ModelConvertHelper<DwShopModel.ShopDownListModel>.ConvertToModel(dt);
            return Success(list);
        }

        [MustLogin]
        [Description("自动填充(商店)")]
        [ReturnType(typeof(IList<DwShopModel.ShopListModel>))]
        public ApiResult GetShopList(
            [ParamDescription("店铺编码或名称")] string Keys = "")
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            string sql = $@"select 
ShopCode,ShopName,ShopId
from ndw.ndw_Shop dw
where dw.ExtrSource='{UserInfo.UserExtrSource}' and dw.CompanyID='{UserInfo.UserCompanyId}'  and 
	(ShopCode ilike '%{Keys}%' or ShopName ilike '%{Keys}%')";

            var dt = Helper.ExecuteDataTable(new VerticaCommand(sql));
            var list = ModelConvertHelper<DwShopModel.ShopListModel>.ConvertToModel(dt);
            return Success(list);
        }


        /// Writer：梁家健
        /// Create Date：2017-05-12
        [MustLogin]
        [Description("店铺下拉列表")]
        [ReturnType(typeof(IList<DwShopModel.ShopListModel>))]
        public ApiResult GetShopListByConditions([ParamDescription("商品区域")] string ParentAreaID = "",
            [ParamDescription("省份")] string ProID = "",
            [ParamDescription("城市")] string CityID = "",
            [ParamDescription("发货仓")] string StockID = "",
            [ParamDescription("店铺名称或编码")] string Keys = "",
            [ParamDescription("零售分部")] string SalePart = "")
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sql = $@"SELECT  shop.ShopID ,
        shop.ShopCode ,
        shop.ShopName
FROM    ndw.ndw_Shop shop
        LEFT JOIN nods.nods_erp_Bas_Area area ON area.AreaID = shop.AreaID
                                                 AND area.ExtrSource = shop.ExtrSource
        LEFT JOIN ndw.ndw_AreaProCity apc ON shop.AreaID = apc.AreaID
                                             AND apc.ExtrSource = shop.ExtrSource
                                             AND apc.CompanyID = shop.CompanyID
WHERE   shop.ExtrSource = '{UserInfo.UserExtrSource}'
        AND shop.CompanyID = '{UserInfo.UserCompanyId}'
        AND ( shop.ShopState = '正常营业'
              OR shop.ShopState = '待开业'
            )
{(string.IsNullOrEmpty(ParentAreaID) ? "" : $@" and apc.ParentAreaID='{ParentAreaID}' ")}
{(string.IsNullOrEmpty(StockID) ? "" : $@" and apc.StockID='{StockID}' ")}
{(string.IsNullOrEmpty(ProID) ? "" : $@" and area.ParentID='{ProID}' ")}
{(string.IsNullOrEmpty(CityID) ? "" : $@" and shop.AreaID='{CityID}' ")}
{(string.IsNullOrEmpty(SalePart) ? "" : $@" and shop.SalePart='{SalePart}' ")}
{(string.IsNullOrEmpty(Keys) ? "" : $@" and (shop.ShopCode like '%{Keys}%' or shop.ShopName like '%{Keys}%')")}
GROUP BY shop.ShopID ,
        shop.ShopCode ,
        shop.ShopName
ORDER BY shop.ShopID ";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var result = ModelConvertHelper<DwShopModel.ShopListModel>.ConvertToModel(dt);
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }


        /// Writer：蔡东明
        /// Create Date：2017-07-10
        [MustLogin]
        [JsonType]
        [Description("店铺下拉列表")]
        [ReturnType(typeof(IList<DwShopModel.ShopListModel>))]
        public ApiResult GetShopListByConditions2([ParamDescription("商品区域")] SearchModel model)
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            var ParentAreaIDStr = "";
            var ProIDStr = "";
            var CityIDStr = "";

            if (model.ParentAreaID != null && model.ParentAreaID.Count > 0)
            {
                ParentAreaIDStr = "'" + string.Join("','", model.ParentAreaID.ToArray()) + "'";
            }
            if (model.ProID != null && model.ProID.Count > 0)
            {
                ProIDStr = "'" + string.Join("','", model.ProID.ToArray()) + "'";
            }
            if (model.CityID != null && model.CityID.Count > 0)
            {
                CityIDStr = "'" + string.Join("','", model.CityID.ToArray()) + "'";
            }
            try
            {
                var sql = $@"select shop.ShopID,shop.ShopCode,shop.ShopName from ndw.ndw_Shop shop
left join nods.nods_erp_Bas_Area area on area.AreaID = shop.AreaID and area.ExtrSource =shop.ExtrSource
left join ndw.ndw_AreaProCity apc on shop.AreaID = apc.AreaID and apc.ExtrSource = shop.ExtrSource and apc.CompanyID = shop.CompanyID
where shop.ExtrSource='{UserInfo.UserExtrSource}' and shop.CompanyID='{UserInfo.UserCompanyId}' 
AND ( shop.ShopState = '正常营业' OR shop.ShopState = '待开业' )
{(string.IsNullOrEmpty(model.StockID) ? "" : $@" and apc.StockID='{model.StockID}' ")}
{(string.IsNullOrWhiteSpace(ParentAreaIDStr) ? "" : $@" and apc.ParentAreaID in ({ParentAreaIDStr}) ")}
{(string.IsNullOrWhiteSpace(ProIDStr) ? "" : $@" and area.ParentID in ({ProIDStr}) ")}
{(string.IsNullOrWhiteSpace(CityIDStr) ? "" : $@" and shop.AreaID in ({CityIDStr}) ")}
{(string.IsNullOrEmpty(model.SalePart) ? "" : $@" and shop.SalePart='{model.SalePart}' ")}
{(string.IsNullOrEmpty(model.Keys) ? "" : $@" and (shop.ShopCode like '%{model.Keys}%' or shop.ShopName like '%{model.Keys}%')")}
group by shop.ShopID,shop.ShopCode,shop.ShopName
order by shop.ShopID ";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var result = ModelConvertHelper<DwShopModel.ShopListModel>.ConvertToModel(dt);
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        [MustLogin]
        [Description("自动填充(商店)")]
        [ReturnType(typeof(IList<DwShopModel.ShopListCityModel>))]
        public ApiResult GetShopListCityModel()
        {
            var list = new List<DwShopModel.ShopListCityModel>();
            string key = "ShopListCityModelAll";
            if (RedisCache.Exists(key))
            {
                list = RedisCache.GetSet<DwShopModel.ShopListCityModel>(key);
            }
            if (list.Count < 1)
            {
                string CompanyId = UserInfo.UserCompanyId;
                string ExtrSource = UserInfo.UserExtrSource;
                string sql = $@"SELECT ShopID,ShopName,Province_Name ProvinceName,City_Name CityName FROM ndw.ndw_Shop  WHERE ShopState = '正常营业' and CompanyID = '{CompanyId}' and ExtrSource = '{ExtrSource}'";
                var dt = Helper.ExecuteDataTable(new VerticaCommand(sql));
                list = ModelConvertHelper<DwShopModel.ShopListCityModel>.ConvertToModel(dt).ToList();
                RedisCache.MaxSetList<DwShopModel.ShopListCityModel>(key, list);
                RedisCache.Expire(key, 10);
            }
            return Success(list);
        }
    }
}
